﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PRC_TB_USER_TASK_HISTORY_SE')
	BEGIN
		DROP  Procedure  dbo.PRC_TB_USER_TASK_HISTORY_SE
	END

GO

create procedure [dbo].[PRC_TB_USER_TASK_HISTORY_SE]
(
	@TUH_USER_ID int = null,
	@TUH_TASK_ID int = null,
	@TUH_PROJECT varchar(80) = null,
	@TUH_CHANGE_REQUEST_RESULT_ID int = null,
	@TUH_CHANGE_REQUEST_MOTIVATION_ID int = null
)
as
	select 
		TUH_ID,
		TUH_USER_ID,
		TUH_TASK_ID,
		TUH_PROJECT,
		TUH_NAME,
		TUH_START_DATE,
		TUH_END_DATE,
		TUH_HOURS_DAY,
		TUH_DESCRIPTION,
		TUH_TYPE,
		TUH_CHANGE_REQUEST_ID,
		TCR_ID,
		TCR_DESCRIPTION,
		TUH_CLASSIFICATION_ID,
		TUHC_DESCRIPTION
	from TB_USER_TASK_HISTORY
		left join TB_CHANGE_REQUEST_RESULTS 
			on TCR_ID = TUH_CHANGE_REQUEST_RESULT_ID
		left join TB_USER_TASK_HISTORY_CHANGE_REQUEST_MOTIVATION
			on TUH_ID = TUHM_HISTORY_ID
		left join TB_USER_TASK_HISTORY_CLASSIFICATION
			on TUH_CLASSIFICATION_ID = TUHC_ID
	where (@TUH_USER_ID is null or TUH_USER_ID = @TUH_USER_ID)
	and (@TUH_TASK_ID is null or TUH_TASK_ID = @TUH_TASK_ID)
	and (@TUH_PROJECT is null or TUH_PROJECT = @TUH_PROJECT)
	and (@TUH_CHANGE_REQUEST_RESULT_ID is null or TUH_CHANGE_REQUEST_RESULT_ID = @TUH_CHANGE_REQUEST_RESULT_ID)
	and (@TUH_CHANGE_REQUEST_MOTIVATION_ID is null or TUHM_CHANGE_REQUEST_MOTIVATION_ID = @TUH_CHANGE_REQUEST_MOTIVATION_ID)
	group by TUH_ID,TUH_USER_ID,TUH_TASK_ID,TUH_PROJECT,TUH_NAME,TUH_START_DATE,TUH_END_DATE,TUH_HOURS_DAY,TUH_DESCRIPTION,TUH_TYPE,TUH_CHANGE_REQUEST_ID,TCR_ID,TCR_DESCRIPTION,TUH_CLASSIFICATION_ID,TUHC_DESCRIPTION
	order by tuh_start_date desc, tuh_name


GO

GRANT EXEC ON dbo.PRC_TB_USER_TASK_HISTORY_SE TO PUBLIC

GO